-- exec sp_load_transit_lots '0', '0', '0', 'XLSREPORT'
ALTER PROCEDURE sp_load_transit_lots (

@PROCESS_STEP_IDS varchar(2000),
@PROCESS_TYPE VARCHAR(50),
@VENDOR_NAME	VARCHAR(50),
@REPORT_TYPE    VARCHAR(50)
)

AS
begin
	
	declare @ship_jit_window int
		
	CREATE TABLE #RESULTS ([Lot Number] varchar(50), [Fab Lot Number] varchar(50), [Part Number] varchar(50), [PO Number] varchar(50),[SO Number] varchar(50),[Invoice No] varchar(50),[Process] varchar(50),
				[From Location] varchar(50), [Ship To] varchar(100), [Ship Date] datetime, [Ship Qty] int, Ps_type_Id int, ShippedLot_Id int)
	
	DECLARE @QUERYSTRING	VARCHAR(4000)

	SELECT @ship_jit_window = Ship_JIT_WINDOW from Client_Table 
	
	IF @ship_jit_window IS NULL
	BEGIN
			SET @ship_jit_window = 0
	END
	SET @QUERYSTRING = N'INSERT INTO #RESULTS select LT.Lot_Number , LT.Fab_Lot_Number, P.Part_Number , ISNULL(SL.SL_Customer_PO,LT.PO_NUMBER) , SL.SL_Customer_SO,
		SL.SL_Invoice_Number , PS.PS_Type, (SELECT L_VENDOR_NAME FROM lOCATION where location_id = LT.Location_ID ) , SL.SL_NextLocation, SL.Ship_Date, 
		SL.Ship_Qty, (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_Type), SL.ShippedLot_Id
		from Lot_Table LT, Shipped_Lot SL, Product P, Process_Step PS
		where SL.Lot_ID = LT.Lot_ID and LT.ProcessStep_ID = P.ProcessStep_ID and P.ProcessStep_ID = PS.ProcessStep_ID
		and SL.Status = ''TRANSIT'' and SL.Ship_Status <= 0 '
		  
	SET @QUERYSTRING = @QUERYSTRING + '  and DATEDIFF(day, SL.Ship_Date, getdate()) >= ' + CONVERT(VARCHAR,@ship_jit_window)

	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
 	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING + '  AND PS_TYPE = ''' + @PROCESS_TYPE + ''' '
	END
	
 	IF ( @PROCESS_STEP_IDS IS NOT NULL AND @PROCESS_STEP_IDS <> '0'  )
 	BEGIN
		SET @QUERYSTRING = @QUERYSTRING +' AND P.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESS_STEP_IDS +''') )'
	END
	IF ( @VENDOR_NAME IS NOT NULL AND @VENDOR_NAME <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME= ''' +@VENDOR_NAME + ''') '
 	END
	--PRINT @QUERYSTRING		
	EXEC (@QUERYSTRING)
	IF @REPORT_TYPE IS NOT NULL AND @REPORT_TYPE = 'XLSREPORT'
	BEGIN
		SELECT [Part Number], [Lot Number], [Fab Lot Number], [PO Number], [SO Number], [Invoice No], [Process],
				[From Location], [Ship To], [Ship Date], [Ship Qty] from #RESULTS order by Ps_type_Id, [Ship Date],[Part Number] desc 
	END	
	ELSE
	BEGIN
		select * from #RESULTS order by Ps_type_Id, [Ship Date],[Part Number] desc 
	END
	
end



